# -*- coding:utf-8 -*-
# @FileName  :read_excel.py
# @Time      :2023/11/22 22:55
# @Author    :lin
import os
import re

from openpyxl import load_workbook

desktopPath = os.path.expanduser("~/Desktop")
path = os.path.join(desktopPath, '1.xlsx')
pathTranslate = os.path.join(desktopPath, '2.xlsx')

translate = {
    'auto': 'auto',
    'TC': 'cht',
    'RU': 'ru',
    'TR': 'tr',
    'TH': 'th',
    'IT': 'it',
    'ID': 'id',
    'VN': 'vie',
    'ES': 'spa',
    'KR': 'kor',
    'JP': 'jp',
    'PT': 'pt',
    'DE': 'de',
    'FR': 'fra',
}


def get_dict(_path):
    body_dict = {}
    data_wb = load_workbook(_path)
    data_value = data_wb[data_wb.sheetnames[0]]
    _language = []
    for j in range(1, data_value.max_column + 1):
        if data_value.cell(1, j).value:
            _language.append(translate[data_value.cell(1, j).value])
    body_dict["language"] = _language
    _body = []
    for i in range(2, data_value.max_row + 1):
        if data_value.cell(i, 1).value:
            _body.append(data_value.cell(i, 1).value)
    body_dict["text"] = _body
    # print(body_dict)
    data_wb.close()
    return body_dict


def write_dict(_path, _res_json):
    wb = load_workbook(path)
    ws = wb[wb.sheetnames[0]]
    column_list = {}
    for i in range(2, ws.max_column + 1):
        column_list[ws.cell(1, i).value] = i
    for item in _res_json:
        for _translations in item["translations"]:
            _text = _translations["text"]
            _to = _translations["to"].lower()
            result = re.findall(r'^(\d+)[ ]?#[ ]?[*][ ]?#[ ]?.[ ]?(.*)', _text)[0]
            row = int(result[0])
            trans = result[1]
            ws.cell(row, column_list[_to]).value = trans
    wb.save(_path)
    wb.close()
    pass

# get_dict(path)
